package com.warm.orm.eq.sys.mapper.impl;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.StrUtil;
import com.easy.query.api4j.client.EasyQuery;
import com.easy.query.api4j.select.Queryable;
import com.easy.query.api4j.select.Queryable4;
import com.easy.query.core.enums.SQLExecuteStrategyEnum;
import com.easy.query.solon.annotation.Db;
import com.warm.orm.domain.PropertySort;
import com.warm.orm.domain.sys.SysDept;
import com.warm.orm.domain.sys.SysRole;
import com.warm.orm.domain.sys.SysUser;
import com.warm.orm.domain.sys.SysUserRole;
import com.warm.orm.eq.sys.mapper.SysUserMapper;
import org.noear.solon.annotation.Component;

import java.util.List;

/**
 * @author 糊搞
 */
@Component
public class SysUserMapperImpl implements SysUserMapper {

    @Db
    private EasyQuery easyQuery;

    @Override
    public List<SysUser> selectUserListByDept(Long deptId) {
        // from sys_user u
        return this.easyQuery.queryable(SysUser.class)
                //  where u.dept_id = #{deptId} and u.del_flag = '0'
                .where(t -> t.eq(SysUser::getDeptId, deptId))
                // order by user_id
                .orderByObject(new PropertySort("userId", Boolean.TRUE))
                .toList();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#checkEmailUnique(java.lang.String)
     */
    @Override
    public SysUser checkEmailUnique(String email) {
        // from sys_user
        return this.easyQuery.queryable(SysUser.class)
                // where email = #{email} and del_flag = '0'
                .where(t -> t.eq(SysUser::getEmail, email))
                // limit 1
                .firstOrNull();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#checkPhoneUnique(java.lang.String)
     */
    @Override
    public SysUser checkPhoneUnique(String phonenumber) {
        // from sys_user
        return this.easyQuery.queryable(SysUser.class)
                // where phonenumber = #{phonenumber} and del_flag = '0'
                .where(t -> t.eq(SysUser::getPhonenumber, phonenumber))
                // limit 1
                .firstOrNull();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#checkUserNameUnique(java.lang.String)
     */
    @Override
    public SysUser checkUserNameUnique(String userName) {
        // from sys_user
        return this.easyQuery.queryable(SysUser.class)
                // where user_name = #{userName} and del_flag = '0'
                .where(t -> t.eq(SysUser::getUserName, userName))
                // limit 1
                .firstOrNull();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#deleteUserById(java.lang.Long)
     */
    @Override
    public int deleteUserById(Long userId) {
        return (int) this.easyQuery.deletable(SysUser.class).whereById(userId).executeRows();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#deleteUserByIds(java.lang.Long[])
     */
    @Override
    public int deleteUserByIds(Long[] userIds) {
        return (int) this.easyQuery.deletable(SysUser.class).whereByIds((List<Long>) Convert.toList(userIds)).executeRows();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#insertUser(com.warm.select.api.orm.domain.sys.SysUser)
     */
    @Override
    public int insertUser(SysUser user) {
        return (int) this.easyQuery.insertable(user).executeRows();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#resetUserPwd(java.lang.String, java.lang.String)
     */
    @Override
    public int resetUserPwd(String userName, String password) {
        // update sys_user
        return (int) this.easyQuery.updatable(SysUser.class)
                // set password = #{password}
                .set(SysUser::getPassword, password)
                // where user_name = #{userName}
                .where(t -> t.eq(SysUser::getUserName, userName))
                .executeRows();
    }

    private Queryable4<SysUser, SysDept, SysUserRole, SysRole> buildLocatedQuery(SysUser user) {
        return this.easyQuery.queryable(SysUser.class)
                // left join sys_dept d on u.dept_id = d.dept_id
                .leftJoin(SysDept.class, (t, t1) -> t1.eq(t, SysDept::getDeptId, SysUser::getDeptId))
                // left join sys_user_role user_role on u.user_id = user_role.user_id
                .leftJoin(SysUserRole.class, (t, t1, t2) -> t2.eq(t, SysUserRole::getUserId, SysUser::getUserId))
                // left join sys_role r on r.role_id = user_role.role_id
                .leftJoin(SysRole.class, (t, t1, t2, t3) -> t3.eq(t2, SysRole::getRoleId, SysUserRole::getRoleId))
                // where u.user_name like concat('%', #{userName}, '%')
                .where((t, t1, t2, t3) -> t.like(StrUtil.isNotBlank(user.getUserName()), SysUser::getUserName, user.getUserName())
                        // AND u.phonenumber like concat('%', #{phonenumber}, '%')
                        .like(StrUtil.isNotBlank(user.getPhonenumber()), SysUser::getPhonenumber, user.getPhonenumber())
                );
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#selectAllocatedList(com.warm.select.api.orm.domain.sys.SysUser)
     */
    @Override
    public List<SysUser> selectAllocatedList(SysUser user) {
        // from sys_user u
        //    left join sys_dept d on u.dept_id = d.dept_id
        //    left join sys_user_role user_role on u.user_id = user_role.user_id
        //    left join sys_role r on r.role_id = user_role.role_id
        // where u.del_flag = '0' and r.role_id = #{roleId}
        //     u.user_name like concat('%', #{userName}, '%')
        //     AND u.phonenumber like concat('%', #{phonenumber}, '%')
        return this.buildLocatedQuery(user)
                // AND u.del_flag = '0' and r.role_id = #{roleId}
                .where((t, t1, t2, t3) -> t3.eq(SysRole::getRoleId, user.getRoleId()))
                .toList();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#selectUnallocatedList(com.warm.select.api.orm.domain.sys.SysUser)
     */
    @Override
    public List<SysUser> selectUnallocatedList(SysUser user) {
        // select u.user_id from sys_user u inner join sys_user_role user_role on u.user_id = user_role.user_id
        Queryable<Long> userIdsQuery = this.easyQuery.queryable(SysUser.class)
                .innerJoin(SysUserRole.class, (t, t1) -> t1.eq(t, SysUserRole::getUserId, SysUser::getUserId))
                .select(Long.class, s -> s.column(SysUser::getUserId));

        // from sys_user u
        //    left join sys_dept d on u.dept_id = d.dept_id
        //    left join sys_user_role user_role on u.user_id = user_role.user_id
        //    left join sys_role r on r.role_id = user_role.role_id
        // where u.user_name like concat('%', #{userName}, '%')
        //     AND u.phonenumber like concat('%', #{phonenumber}, '%')
        return this.buildLocatedQuery(user)
                // AND (r.role_id != #{roleId} or r.role_id IS NULL)
                .where((t, t1, t2, t3) -> t3.and(o -> o.ne(SysRole::getRoleId, user.getRoleId()).or().isNull(SysRole::getRoleId))
                        // AND u.user_id not in (select u.user_id from sys_user u inner join sys_user_role user_role on u.user_id = user_role.user_id)
                        .then(t.notIn(SysUser::getUserId, userIdsQuery))
                        // AND user_role.role_id = #{roleId})
                        .then(t2.eq(SysUserRole::getRoleId, user.getRoleId()))
                )
                //  select distinct u.*
                .distinct().toList();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#selectUserById(java.lang.Long)
     */
    @Override
    public SysUser selectUserById(Long userId) {
        return this.easyQuery.queryable(SysUser.class).whereById(userId)
                .include(t -> t.one(SysUser::getDept))
                .include(t -> t.many(SysUser::getRoles))
                .firstOrNull();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#selectUserByUserName(java.lang.String)
     */
    @Override
    public SysUser selectUserByUserName(String userName) {
        // from sys_user u
        return this.easyQuery.queryable(SysUser.class)
                // where u.user_name = #{userName} and u.del_flag = '0'
                .where(t -> t.eq(SysUser::getUserName, userName))
                // limit 1
                .firstOrNull();
    }

    private Queryable<SysUser> buildListQuery(SysUser sysUser) {
        String beginTime = (String) sysUser.getParams().get("beginTime");
        String endTime = (String) sysUser.getParams().get("endTime");

        // from sys_user
        return this.easyQuery.queryable(SysUser.class)
                // left join sys_dept d on u.dept_id = d.dept_id
                .leftJoin(SysDept.class, (t, t1) -> t1.eq(t, SysDept::getDeptId, SysUser::getDeptId))
                // where u.del_flag = '0' AND u.user_id = #{userId}
                .where((t, t1) -> t.eq(ObjUtil.isNotNull(sysUser.getUserId()), SysUser::getUserId, sysUser.getUserId())
                        // AND u.user_name like concat('%', #{userName}, '%')
                        .like(StrUtil.isNotBlank(sysUser.getUserName()), SysUser::getUserName, sysUser.getUserName())
                        // AND u.status = #{status}
                        .eq(StrUtil.isNotBlank(sysUser.getStatus()), SysUser::getStatus, sysUser.getStatus())
                        // AND u.phonenumber like concat('%', #{phonenumber}, '%')
                        .like(StrUtil.isNotBlank(sysUser.getPhonenumber()), SysUser::getPhonenumber, sysUser.getPhonenumber())
                        // AND date_format(u.create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
                        .and(ObjUtil.isNotNull(beginTime),
                                o -> o.sqlNativeSegment("date_format({0}, ''%y%m%d'') >= date_format({1}, ''%y%m%d'')",
                                        s -> s.expression(SysUser::getCreateTime).value(beginTime)
                                )
                        )
                        // AND date_format(u.create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
                        .and(ObjUtil.isNotNull(endTime),
                                o -> o.sqlNativeSegment("date_format({0}, ''%y%m%d'') <= date_format({1}, ''%y%m%d'')",
                                        s -> s.expression(SysUser::getCreateTime).value(endTime)
                                )
                        )
                        // AND (u.dept_id = #{deptId} OR u.dept_id IN ( {subSelect} ))
                        .and(ObjUtil.isNotNull(sysUser.getDeptId()), o -> o.eq(SysUser::getDeptId, sysUser.getDeptId())
                                .or()
                                .in(SysUser::getDeptId,
                                        // {subSelect} = SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId}, ancestors)
                                        // FROM sys_dept t
                                        this.easyQuery.queryable(SysDept.class)
                                                // WHERE find_in_set(#{deptId}
                                                .where(w -> w.sqlNativeSegment("FIND_IN_SET({0}, ({1}))", c -> c.value(sysUser.getDeptId()).expression(SysDept::getAncestors)))
                                                // SELECT t.dept_id
                                                .select(Long.class, d -> d.column(SysDept::getDeptId))
                                )
                        )

                );
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#selectUserList(com.warm.select.api.orm.domain.sys.SysUser)
     */
    @Override
    public List<SysUser> selectUserList(SysUser sysUser) {
        return this.buildListQuery(sysUser).toList();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#updateUser(com.warm.select.api.orm.domain.sys.SysUser)
     */
    @Override
    public int updateUser(SysUser user) {
        return (int) this.easyQuery.updatable(user).setSQLStrategy(SQLExecuteStrategyEnum.ONLY_NOT_NULL_COLUMNS).executeRows();
    }

    /* (non-Javadoc)
     * @see mapper.com.warm.orm.eq.sys.SysUserMapper#updateUserAvatar(java.lang.String, java.lang.String)
     */
    @Override
    public int updateUserAvatar(String userName, String avatar) {
        // update sys_user
        return (int) this.easyQuery.updatable(SysUser.class)
                // set avatar = #{avatar}
                .set(SysUser::getAvatar, avatar)
                // where user_name = #{userName}
                .where(t -> t.eq(SysUser::getUserName, userName))
                .executeRows();
    }

}
